﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

using System.IO;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;

// for smartfileretrieve
using HtmlAgilityPack;
using ICSharpCode.SharpZipLib.Zip;
using System.Net;

namespace MercuryFE
{
    static partial class LauncherLib
    {

        #region SmartFileRetrieve

        /// <summary>
        /// using the MameFileList.xml file, retrieves all downloadble file links for the specified file type
        /// </summary>
        /// <param name="fileType">File type - as it appears in MameFileList, eg command</param>
        /// <returns>Dictionary of links, with key being display field, and value being the actual link</returns>
        public static Dictionary<string, string> getFileLinks(string fileType, ref string zippattern, ref string linkdisplay)
        {
            // retreive info from xml file

            string filePath = Path.GetDirectoryName(Application.ExecutablePath) + @"\DataFiles\MameFileList.xml";

            var fileSourceDoc = XElement.Load(filePath);
            var query = (from x in fileSourceDoc.Descendants("FileType")
                        where x.Attribute("name").Value == fileType
                        select new
                        {
                            type = x.Attribute("type").Value
                            ,
                            pattern = x.Attribute("urlpattern").Value
                            ,
                            zippattern = x.Attribute("zippattern").Value
                            ,
                            links =
                              (
                              from y in x.Descendants("Link")
                              select y.Attribute("value").Value
                              ).ToList()
                            ,
                            linkDisplay = x.Attribute("linkdisplay").Value
                        }).SingleOrDefault();

            if (query != null)
            {
                zippattern = query.zippattern;
                linkdisplay = query.linkDisplay;
                if (query.type == "list")
                    return getFileLinks(query.links, query.pattern);
                else if (query.type == "direct")
                {
                    Dictionary<string, string> fileLinks = new Dictionary<string, string>();
                    fileLinks.Add("Latest File", query.links[0]);
                    return fileLinks;
                }
                else
                    return null;
            }
            else
                return null;
        }

        /// <summary>
        /// Download and unzip the specified file at the url, placing it into the destination folder
        /// </summary>
        /// <param name="fileLink">The url which returns the file</param>
        /// <param name="destinationFolder">The folder where to unzip the specified file</param>
        public static string downloadFile(string fileLink, string destinationFolder, string zippattern)
        {
            MemoryStream fileStream = downloadFile(fileLink);

            string fileName = "";
            unZipFile(fileStream, destinationFolder, zippattern, ref fileName);

            return fileName;
        }


        private static MemoryStream downloadFile(string fileURL)
        {
            WebClient client = new WebClient();
            byte[] fileStream = client.DownloadData(fileURL);

            MemoryStream ms = new MemoryStream(fileStream);
            return ms;
        }


        private static bool unZipFile(MemoryStream fileStream, string baseDirectory, string zippattern, ref string fileName)
        {

           
            // copied/modified from http://www.logiclabz.com/c/unzip-files-in-net-c-using-sharpziplib-open-source-library.aspx
            bool ret = true;
            try
            {

                //string baseDirectory = Path.GetDirectoryName(InputPathOfZipFile);  

                using (ZipInputStream ZipStream = new ZipInputStream(fileStream))
                {
                    ZipEntry theEntry;
                    while ((theEntry = ZipStream.GetNextEntry()) != null)
                    {
                        if (theEntry.IsFile)
                        {
                            if (theEntry.Name != "")
                            {
                                if (theEntry.Name.ToUpper().EndsWith(zippattern.ToUpper()) || zippattern == "")
                                {

                                    string strNewFile = @"" + baseDirectory + @"\" + theEntry.Name;

                                    if (fileName == "")
                                        fileName = strNewFile;

                                    if (File.Exists(strNewFile))
                                    {
                                        File.Delete(strNewFile);
                                        //continue;
                                    }

                                    using (FileStream streamWriter = File.Create(strNewFile))
                                    {
                                        int size = 2048;
                                        byte[] data = new byte[2048];
                                        while (true)
                                        {
                                            size = ZipStream.Read(data, 0, data.Length);
                                            if (size > 0)
                                                streamWriter.Write(data, 0, size);
                                            else
                                                break;
                                        }
                                        streamWriter.Close();
                                    }

                                    if (theEntry.Name.ToUpper().EndsWith(zippattern.ToUpper()))
                                    {
                                        break;
                                    }
                                }
                            }
                        }
                            // only bother creating sub directories if a pattern hasn't been specified...
                        else if (theEntry.IsDirectory && zippattern=="")
                        {
                            string strNewDirectory = @"" + baseDirectory + @"\" + theEntry.Name;
                            if (!Directory.Exists(strNewDirectory))
                            {
                                Directory.CreateDirectory(strNewDirectory);
                            }
                        }
                    }
                    ZipStream.Close();
                }
            }

            catch (Exception ex)
            {
                //ex.Dump();
                ret = false;
            }
            return ret;
        }


        // Define other methods and classes here
        private static Dictionary<string, string> getFileLinks(List<string> urls, string wildcard)
        {

            Dictionary<string, string> fileColl = new Dictionary<string, string>();

            foreach (string url in urls)
            {

                string[] urlChunks = url.Split('/');


                string baseURL = "";
                for (int i = 0; i < urlChunks.Length - 1; i++)
                {
                    baseURL += urlChunks[i] + "/";
                }

                HtmlWeb hw = new HtmlWeb();


                HtmlAgilityPack.HtmlDocument doc = hw.Load(url);
                //hw.StatusCode.Dump();	


                List<string> fileLinks = new List<string>();
                foreach (HtmlNode link in doc.DocumentNode.SelectNodes("//a[@href]"))
                {
                    HtmlAttribute att = link.Attributes["href"];
                    string fileLink = att.Value;

                    string[] fileChunks = fileLink.Split('/');
                    string fileName = fileChunks[fileChunks.Length - 1];

                    // now check if fileName matches pattern
                    if (fileMatchesPattern(fileName, wildcard))
                    {
                        if (fileLink.StartsWith("./"))
                            fileLink = baseURL + fileLink.Substring(2);

                        fileLinks.Add(fileLink);

                    }

                }
                //		fileLinks.Sort();
                //		fileLinks.Reverse(); // put latest first


                foreach (string file in fileLinks)
                {
                    fileColl.Add(Path.GetFileNameWithoutExtension(file), file);
                }

            }

            return fileColl; //.OrderByDescending (c => c.Key);
        }

        private static bool fileMatchesPattern(string fileName, string pattern)
        {
            // simple check - compare lengths
            if (fileName.Length != pattern.Length)
                return false;

            // now go through character by character - comparing
            for (int i = 0; i < fileName.Length; i++)
            {
                if (pattern[i] != '?' && fileName[i] != pattern[i])
                    return false;
            }

            return true;

        }

        #endregion



        public static bool generateMameInfo(string outFile, ref string error)
        {
            // need to execute mame executable with -listxml option and pipe to file...
            string arguments = "-listxml";
            // find first mame entry
            string executable = "";
            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    executable = (from e in db.Emulator
                                 where e.IsMame == 1
                                 select e.Executable).Take(1).Single();
                }
            }
            if (executable != "")
            {
                string commandline = executable + " " + arguments;

                ProcessStartInfo theProcess = new ProcessStartInfo(executable);
                theProcess.WindowStyle = ProcessWindowStyle.Normal;
                theProcess.CreateNoWindow = true;
                theProcess.Arguments = arguments;
                theProcess.WorkingDirectory = Path.GetDirectoryName(executable);
                theProcess.UseShellExecute = false;
                theProcess.RedirectStandardOutput = true;


                try
                {
                    Process process = Process.Start(theProcess);
                    string output = process.StandardOutput.ReadToEnd();
                    process.WaitForExit();

                    File.WriteAllText(outFile, output);
                }
                catch (Exception e)
                {
                    // set back to standard, since process isn't running 
                    string errorMessage = "Error generating MameInfo file !" + Environment.NewLine
                        + "Message: " + e.Message;
                    return false;
                }

                return true;

            } else {
                error = "No valid Mame emulator definition found.";
                return false;
            }
        }

        public static bool populateMusic()
        {
            string error = "";
            
            string musicPath = getConfigItem("Music Path");

            // scan the folder for all supported music files
            string[] mp3Files = Directory.GetFiles(musicPath, "*.mp3", SearchOption.AllDirectories);

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                SqlCeTransaction transaction = ceConn.BeginTransaction();

                truncateTable(ceConn, "JBQueue", "JBQueuekID", ref error);
                truncateTable(ceConn, "JBCollectionTrack", "", ref error);
                truncateTable(ceConn, "JBCollection", "JBCollectionID", ref error);
                truncateTable(ceConn, "JBTrack", "JBTrackID", ref error);



                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {



                    foreach (string mp3file in mp3Files)
                    {
                        TagLib.File file = TagLib.File.Create(mp3file);
                        TagLib.Id3v1.Tag id3v1Tag = file.GetTag(TagLib.TagTypes.Id3v1) as TagLib.Id3v1.Tag;

                        // simplistic.
                        string title = id3v1Tag.Title;
                        string artists = "Unknown";
                        if (id3v1Tag.Performers.Length > 0)
                        {
                            artists = id3v1Tag.Performers[0];
                        }
                        string genre = "Unknown";
                        if (id3v1Tag.Genres.Length > 0)
                        {
                            genre = id3v1Tag.Genres[0];
                        }
                        int trackNum = (int)id3v1Tag.Track;

                        
                        // if no album name, grab the name of the folder its in...
                        string album = id3v1Tag.Album ?? (Path.GetDirectoryName(mp3file).Split('\\').Last());
                        string albumArtists = "";
                        if (id3v1Tag.Album == null)
                            albumArtists = "Misc";
                        else
                            albumArtists = artists;

                        JBTrack track = new JBTrack()
                        {
                             Artists = artists
                             , Genre = genre
                             , Name = title
                             , Length = (int?)file.Properties.Duration.TotalSeconds
                             , TrackFile = mp3file
                             , NumberTimesPlayed = 0
                        };

                        db.JBTrack.InsertOnSubmit(track);
                        db.SubmitChanges();

                        int collectionId = populateAlbum(album, albumArtists, genre, Path.GetDirectoryName(mp3file), "");
                        JBCollectionTrack ct = new JBCollectionTrack()
                        {
                               JBCollectionID = collectionId
                             , JBTrackID = track.JBTrackID
                             , TrackOrder = trackNum
                             
                        };
                        db.JBCollectionTrack.InsertOnSubmit(ct);
                        db.SubmitChanges();
                    }
                    
                }
                transaction.Commit();
            }

            return true;
        }

        private static int populateAlbum(string album, string artists, string genre, string path, string coverImage)
        {
            int? collectionId = null;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // first check if it exists...
                    collectionId = (from c in db.JBCollection
                                    where c.Name == album && c.IsAlbum == 1
                                    select c.JBCollectionID).SingleOrDefault();

                    if (collectionId != null && collectionId > 0)
                    {
                        return (int)collectionId;
                    }
                    else
                    {
                        // need to create
                        JBCollection col = new JBCollection()
                        {
                             Name = album
                             , IsAlbum = 1
                             , Source = path
                             , Artists = artists
                             , Genre = genre
                             , CoverImage = getCoverImage(path)
                        };

                        db.JBCollection.InsertOnSubmit(col);
                        db.SubmitChanges();

                        return col.JBCollectionID;
                    }

                }
            }
        }

        /// <summary>
        /// Searches the specified path for a image that can be used as a cover image for the album
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static string getCoverImage(string path)
        {
            //List<string> imageList = new List<string>();
            Dictionary<string, string> imageList = new Dictionary<string, string>();

            string[] images = Directory.GetFiles(path, "*.jpg");
            foreach (string image in images)
            {
                imageList.Add(Path.GetFileName(image), image);
            }

            images = Directory.GetFiles(path, "*.png");
            foreach (string image in images)
            {
                imageList.Add(Path.GetFileName(image), image);
            }


            if (imageList.Count > 0)
            {
                // based on order of preference..........
                if (imageList.ContainsKey("front.jpg"))
                {
                    return imageList["front.jpg"];
                }
                if (imageList.ContainsKey("cover.jpg"))
                {
                    return imageList["cover.jpg"];
                }
                if (imageList.ContainsKey("folder.jpg"))
                {
                    return imageList["folder.jpg"];
                }

                // otherwise, just return the first found image
                return ((KeyValuePair<string,string>)imageList.First()).Value;
            }


            return null;

        }
        //private static List<string> _versions = null;

        public static void populateMameDB(string mameInfoFile, string controlFile, string historyFile, string categoryFile, string nPlayersFile, string commandFile
            , ref ToolStripStatusLabel sl, ref string error)
        {


            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                
                SqlCeTransaction transaction = ceConn.BeginTransaction();
                DateTime start = DateTime.Now;
                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {
                    Dictionary<string, int> romLookup = null;
                    //_versions = new List<string>();

                    sl.Text = "Commencing data load. ";

                    if (mameInfoFile.Trim() != "" && File.Exists(mameInfoFile))
                    {
                        sl.Text = "Loading MameInfo file...";
                        populateMameRom(mameInfoFile, db);
                        sl.Text = "Generating Bios lookup...";
                        populateMameRomBios(db, ceConn, ref error);
                        sl.Text = "Generating Mame Rom Dependencies...";
                        populateMameRomDependencies(db, ceConn, ref error);
                    }

                    // populate lookup for other files
                    romLookup = (from r in db.MameRom
                                 select r).ToDictionary(r => r.RomName, r => r.MameRomID);

                    if (romLookup == null || romLookup.Count == 0)
                    {
                        MessageBox.Show("No MameInfo has been populated - not loading other files.");
                    }
                    else
                    {

                        if (nPlayersFile.Trim() != "" && File.Exists(nPlayersFile))
                        {
                            sl.Text = "Loading nPlayers file...";
                            populateMameNPlayers(nPlayersFile, romLookup, db, ceConn, ref error);
                        }

                        if (categoryFile.Trim() != "" && File.Exists(categoryFile))
                        {
                            sl.Text = "Loading Category file...";
                            populateMameCategories(categoryFile, romLookup, db, ceConn, ref error);
                        }

                        if (commandFile.Trim() != "" && File.Exists(commandFile))
                        {
                            sl.Text = "Loading Command file...";
                            populateMameCommand(commandFile, romLookup, db, ceConn, ref error);
                        }

                        if (historyFile.Trim() != "" && File.Exists(historyFile))
                        {
                            sl.Text = "Loading History file...";
                            populateMameHistory(historyFile, romLookup, db, ceConn, ref error);
                        }

                        if (controlFile.Trim() != "" && File.Exists(controlFile))
                        {
                            sl.Text = "Loading Control file...";
                            populateMameControlsInfo(controlFile, romLookup, db, ceConn, ref error);
                            populateMamePlayers(controlFile, romLookup, db, ceConn, ref error);
                        }
                    }

                    sl.Text = "Data loaded. Committing to database...";

                   // MessageBox.Show(string.Join("\n",_versions.ToArray()));

                }
                transaction.Commit();
                DateTime end = DateTime.Now;
                TimeSpan span = end.Subtract(start);



                sl.Text = "Data Load finished in " + span.TotalSeconds.ToString() + " seconds.";
            }

            //System.Windows.Forms.MessageBox.Show("Populate DB Finished");

        }

        /// <summary>
        /// Goes through all the specified emulators, and their corresponding paths, and populates these into the database.
        /// </summary>
        /// <param name="clearBeforeLoad">If true, truncates the game table, otherwise, updates records in place</param>
        public static bool populateGameEntries(bool clearBeforeLoad, ref ToolStripStatusLabel sl, ref string error)
        {
            int insertCount = 0
                , updateCount = 0
                , deleteCount = 0;

            using (SqlCeConnection ceConn = new SqlCeConnection(getDBConnection()))
            {
                ceConn.Open();
                if (clearBeforeLoad)
                {
                    // have to clear out collections first..
                    if(!truncateTable(ceConn, "MenuCollection", "", ref error))
                        return false;
                    if(!truncateTable(ceConn, "Game", "GameID", ref error))
                        return false;

                }

                SqlCeTransaction transaction = ceConn.BeginTransaction();

                using (LauncherDB db = new LauncherDB(ceConn)) // linq
                {

                    // go through each emulator 
                    foreach (var emulator in (from e in db.Emulator where e.EmulatorID > 0 select new { emulatorId = (int)e.EmulatorID, IsMame = (e.IsMame==1?true:false) }).ToList())
                    {
                        
                        Dictionary<string, GameEntry> games = new Dictionary<string, GameEntry>();
                        // first get all the ROM paths
                        var romPaths = (from x in db.EmulatorPath
                                        where x.EmulatorID == emulator.emulatorId
                                        where x.PathType == "ROM"
                                        select new
                                        {
                                            x.Path
                                            ,
                                            IncludeSubPaths = (x.IncludeSubPaths == 1 ? true : false)
                                            ,
                                            x.EmulatorPathID
                                            ,
                                            x.SearchPattern
                                        }).ToList();

                        // if there are no rom paths associated with the emulator, don't bother continuing (nothing to load against)
                        if (romPaths.Count == 0)
                        {
                            continue; // go to next emulator
                        }

                        Dictionary<string, object[]> mameGames = null;

                        // if the emulator we are looking at is mame, then grab all the information about the games, used to associate the mame rom 
                        if (emulator.IsMame)
                        {
                            // load all mame info if it exists
                            mameGames = (
                                from m in db.MameRom
                                select new
                                {
                                    m.MameRomID
                                    ,
                                    m.RomName
                                    ,
                                    temp = new object[] { m.MameRomID, m.GameName }
                                }
                                ).ToDictionary(q => q.RomName, p => p.temp);
                        }

                        foreach (var path in romPaths)
                        {
                            sl.Text = "Processing " + path.Path + " [ " + path.SearchPattern + "]....";
                            // scan the file system
                            DirectoryInfo currentPath = new DirectoryInfo(path.Path);
                            FileInfo[] roms = currentPath.GetFiles(path.SearchPattern, (path.IncludeSubPaths ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly));

                            foreach (FileInfo file in roms)
                            {
                                string subpath = file.DirectoryName != path.Path ? file.DirectoryName.Substring(path.Path.Length) : "";
                                string romname = file.Name.Substring(0, file.Name.Length - file.Extension.Length);
                                if (!games.ContainsKey(romname))
                                {
                                    int mameRomId = 0;
                                    string gameName = romname;
                                    if (emulator.IsMame && mameGames.ContainsKey(romname))
                                    {
                                        mameRomId = (int)mameGames[romname][0];
                                        gameName = (string)mameGames[romname][1];
                                    } else if (emulator.IsMame && mameGames.Count > 0 &&  !mameGames.ContainsKey(romname))
                                    {
                                        // scanning mame games, and this rom isn't in database (and there are entries in database, meaning a load has been undertakeN)
                                        continue;
                                    }

                                    GameEntry currentGame = new GameEntry()
                                    {
                                        emulatorId = emulator.emulatorId
                                        ,
                                        romFile = file.Name
                                        ,
                                        romPathId = path.EmulatorPathID
                                        ,
                                        romSubPath = subpath
                                        ,
                                        name = gameName
                                    };

                                    if (mameRomId > 0)
                                        currentGame.mameRomId = mameRomId;

                                    games.Add(romname, currentGame);
                                }
                            }

                        }


                        var otherPaths = (from x in db.EmulatorPath
                                          where x.EmulatorID == emulator.emulatorId
                                          where x.PathType != "ROM"
                                          select new
                                          {
                                              x.Path
                                              ,
                                              IncludeSubPaths = (x.IncludeSubPaths == 1 ? true : false)
                                              ,
                                              x.EmulatorPathID
                                              ,
                                              x.SearchPattern
                                              ,
                                              x.PathType
                                          }).ToList();

                        foreach (var path in otherPaths)
                        {
                            // scan the file system
                            DirectoryInfo currentPath = new DirectoryInfo(path.Path);
                            FileInfo[] roms = currentPath.GetFiles(path.SearchPattern, (path.IncludeSubPaths ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly));

                            foreach (FileInfo file in roms)
                            {
                                string subpath = file.DirectoryName != path.Path ? file.DirectoryName.Substring(path.Path.Length) : "";
                                string romname = file.Name.Substring(0, file.Name.Length - file.Extension.Length);

                                if (games.ContainsKey(romname))
                                {
                                    GameEntry game = games[romname];

                                    if (path.PathType == "SNAP")
                                    {
                                        game.snapshotFile = file.Name;
                                        game.snapshotPathId = path.EmulatorPathID;
                                        game.snapshotSubPath = subpath;
                                    }
                                    else if (path.PathType == "MOVIE")
                                    {
                                        game.movieFile = file.Name;
                                        game.moviePathId = path.EmulatorPathID;
                                        game.movieSubPath = subpath;

                                    }
                                }
                            }
                        }

                        // fix up key on games
                        Dictionary<string, GameEntry> actualGames = new Dictionary<string, GameEntry>();
                        foreach (KeyValuePair<string, GameEntry> entry in games)
                        {
                            actualGames.Add(entry.Value.romFile, entry.Value);
                        }
                        // actual games contains listing of whats on file system (as opposed to loaded in DB)

                        // now load all present in the database

                        Dictionary<string, GameEntry> loadedGames =
                                (from x in db.Game
                                 where x.EmulatorID == emulator.emulatorId
                                 select new
                                 {
                                     romFile = x.RomFile
                                     ,
                                     entry = new GameEntry
                                     {
                                         gameId = x.GameID
                                         ,
                                         name = x.Name
                                         ,
                                         romFile = x.RomFile
                                         ,
                                         romPathId = x.RomPathID
                                         ,
                                         romSubPath = x.RomSubPath
                                         ,
                                         snapshotFile = x.SnapshotFile
                                         ,
                                         snapshotPathId = x.SnapshotPathID //?? 0
                                         ,
                                         snapshotSubPath = x.SnapshotSubPath
                                         ,
                                         movieFile = x.MovieFile
                                         ,
                                         moviePathId = x.MoviePathID //?? 0
                                         ,
                                         movieSubPath = x.MovieSubPath
                                         ,
                                         mameRomId = x.MameRomID //?? 0
                                     }

                                 }).ToDictionary(a => a.romFile, b => b.entry);


                        foreach (KeyValuePair<string, GameEntry> entry in actualGames)
                        {
                            // check if already loaded
                            if (loadedGames.ContainsKey(entry.Key))
                            {
                                // check if different (needs an update)
                                if (entry.Value.hash != loadedGames[entry.Key].hash)
                                {
                                    // UPDATE
                                    updateCount++;

                                    Game g = db.Game.Single(x => x.GameID == loadedGames[entry.Key].gameId);
                                    g.MameRomID = entry.Value.mameRomId;
                                    g.SnapshotFile = entry.Value.snapshotFile;
                                    g.SnapshotPathID = entry.Value.snapshotPathId;
                                    g.SnapshotSubPath = entry.Value.snapshotSubPath;
                                    g.MovieFile = entry.Value.movieFile;
                                    g.MoviePathID = entry.Value.moviePathId;
                                    g.MovieSubPath = entry.Value.movieSubPath;
                                    //db.SubmitChanges();
                                }
                                // else ignore...

                            }
                            else
                            {
                                // INSERT
                                insertCount++;

                                Game g = new Game()
                                {
                                    EmulatorID = entry.Value.emulatorId
                                     ,
                                    MameRomID = entry.Value.mameRomId
                                     ,
                                    RomFile = entry.Value.romFile
                                     ,
                                    RomPathID = entry.Value.romPathId
                                     ,
                                    RomSubPath = entry.Value.romSubPath
                                     ,
                                    MovieFile = entry.Value.movieFile
                                     ,
                                    MovieSubPath = entry.Value.movieSubPath
                                     ,
                                    Name = entry.Value.name
                                     ,
                                    SnapshotFile = entry.Value.snapshotFile
                                     ,
                                    SnapshotSubPath = entry.Value.snapshotSubPath
                                };


                                if (entry.Value.moviePathId > 0)
                                    g.MoviePathID = entry.Value.moviePathId;

                                if (entry.Value.snapshotPathId > 0)
                                    g.SnapshotPathID = entry.Value.snapshotPathId;


                                db.Game.InsertOnSubmit(g);
                            }
                        }
                        db.SubmitChanges();


                        // go the other direction, and remove loaded games, which aren't on file system
                        foreach (KeyValuePair<string, GameEntry> entry in loadedGames)
                        {
                            if (!actualGames.ContainsKey(entry.Key))
                            {
                                Game g = db.Game.Single(x => x.GameID == loadedGames[entry.Key].gameId);

                                db.Game.DeleteOnSubmit(g);
                                deleteCount++;
                            }
                        }
                        db.SubmitChanges();
                    }
                    transaction.Commit();
                }
            }
            sl.Text = "Game scan complete. [insert:" + insertCount.ToString() + " update:" + updateCount.ToString() + " delete:" + deleteCount.ToString() + "]";
            return true;
        }

        /// <summary>
        /// Populates the mameinfo file (provided by mame.exe -listxml). The table isn't truncated, so we retain the id value
        /// for all other lookups (namely, the Game entries).
        /// </summary>
        /// <param name="mameInfoFile">Full path to the source file</param>
        /// <param name="db">Reference to LauncherDB</param>
        /// <returns>A dictionary lookup to roms and their corresponding id.</returns>
        private static void populateMameRom(string mameInfoFile, LauncherDB db)
        {
            var mameInfoDoc = XElement.Load(mameInfoFile);

            string mameVersion = (string)mameInfoDoc.Attribute("build");

            int insertCount = 0;
            int updateCount = 0;

            Dictionary<string, mameRomCompare> romCompare = null;

            romCompare = (from r in db.MameRom
                         select r).ToDictionary(r => r.RomName, r => new mameRomCompare(r.MameRomID, r.Hash));

            // load in from xml files
            List<GameDetail> games = (
                from mameinfo in mameInfoDoc.Descendants("game")
                select new GameDetail
                {
                    romName = (string)mameinfo.Attribute("name")
                    ,
                    cloneOf = (string)mameinfo.Attribute("cloneof")
                    ,
                    romOf = (string)mameinfo.Attribute("romof")
                    ,
                    description = (string)mameinfo.Element("description")
                    ,
                    sourceFile = (string)mameinfo.Attribute("sourcefile")
                    ,
                    year = (string)mameinfo.Element("year")
                    ,
                    manufacturer = (string)mameinfo.Element("manufacturer")
                    ,
                    driverStatus = (string)mameinfo.Element("driver").Attribute("status")
                    ,
                    driverEmulation = (string)mameinfo.Element("driver").Attribute("emulation")
                    ,
                    driverColor = (string)mameinfo.Element("driver").Attribute("color")
                    ,
                    driverSound = (string)mameinfo.Element("driver").Attribute("sound")
                    ,
                    driverGraphic = (string)mameinfo.Element("driver").Attribute("graphic")
                    ,
                    driverSavestate = (string)mameinfo.Element("driver").Attribute("savestate")
                    ,
                    driverPalettesize = (string)mameinfo.Element("driver").Attribute("palettesize")
                    ,
                    rotate = (int?)(mameinfo.Elements("display").Any() ? mameinfo.Element("display").Attribute("rotate") : null)
                    ,
                    displayType = (mameinfo.Elements("display").Any() ? (string)mameinfo.Element("display").Attribute("type") : "")
                    ,
                    isBios = ((mameinfo.Attribute("isbios") == null ? "no" : (string)mameinfo.Attribute("isbios")) == "yes" ? true : false)
                }
            ).ToList();

            games.Sort(new Comparison<GameDetail>(SortByRomName));


            //_versions.Add("MameInfo: " + mameVersion);


            foreach (GameDetail game in games)
            {

                // if record already in database
                if (romCompare.ContainsKey(game.romName))
                {
                    // check hash
                    if (game.getHash() != romCompare[game.romName].Hash)
                    {
                        // update
                        MameRom mr = db.MameRom.Single(x => x.MameRomID == romCompare[game.romName].MameRomID);
                        mr.GameName = game.gameName;
                        mr.CloneOf = game.cloneOf;
                        mr.RomOf = game.romOf;
                        mr.Description = game.description;
                        mr.SourceFile = game.sourceFile;
                        mr.Year = game.year;
                        mr.Manufacturer = game.manufacturer;
                        mr.DriverStatus = game.driverStatus;
                        mr.DriverEmulation = game.driverEmulation;
                        mr.DriverColor = game.driverColor;
                        mr.DriverSound = game.driverSound;
                        mr.DriverGraphic = game.driverGraphic;
                        mr.DriverSavestate = game.driverSavestate;
                        mr.DriverPalettesize = game.driverPalettesize;
                        mr.DisplayType = game.displayType;
                        mr.Rotate = game.rotate;
                        mr.IsBios = (byte?)(game.isBios?1:0);
                        mr.Hash = game.getHash();


                        db.SubmitChanges();
                        updateCount++;
                    }
                }
                else
                {
                    // insert
                    MameRom mr = new MameRom
                    {
                        RomName = game.romName
                        ,
                        GameName = (game.gameName != null ? game.gameName : game.description) // set name to description if game name is null
                        ,
                        CloneOf = game.cloneOf
                        ,
                        RomOf = game.romOf
                        ,
                        Description = game.description
                        ,
                        SourceFile = game.sourceFile
                        ,
                        Year = game.year
                        ,
                        Manufacturer = game.manufacturer
                        ,
                        DriverStatus = game.driverStatus
                        ,
                        DriverEmulation = game.driverEmulation
                        ,
                        DriverColor = game.driverColor
                        ,
                        DriverSound = game.driverSound
                        ,
                        DriverGraphic = game.driverGraphic
                        ,
                        DriverSavestate = game.driverSavestate
                        ,
                        DriverPalettesize = game.driverPalettesize
                        ,
                        DisplayType = game.displayType
                        ,
                        Rotate = game.rotate
                     
                        , IsBios= (byte?)(game.isBios?1:0)
                        ,Hash = game.getHash()
                    };
                    db.MameRom.InsertOnSubmit(mr);
                    db.SubmitChanges();
                    insertCount++;
                }
            }


            romCompare.Clear();
            games.Clear();


            populateMameFileInfo(db, "MameFileInfo", mameVersion);

        }


        private static bool populateMameRomBios(LauncherDB db, SqlCeConnection ceConn, ref string error)
        {

            // need two passes on mameRom -

            if(!truncateTable(ceConn, "MameRomBios", "", ref error))
                return false;

            Dictionary<string,int> biosLookup = (from mr in db.MameRom
                             where mr.IsBios == 1
                             select new {mr.MameRomID, mr.RomName}
                             ).ToDictionary(a => a.RomName, b=> b.MameRomID );


            // first parse -  pick up 'originals', romof value contains bios
            var romBiosParse1 = (from mr in db.MameRom
                         where mr.CloneOf == null && biosLookup.Select(a=> a.Key).ToArray().Contains(mr.RomOf) && mr.IsBios == 0
                         select new 
                         {
                               BiosMameRomID = biosLookup[mr.RomOf]
                             , MameRomID =  mr.MameRomID
                         }).ToList();

            foreach (var entry in romBiosParse1)
            {
                MameRomBios mrb = new MameRomBios()
                {
                     BiosMameRomID = entry.BiosMameRomID
                     , MameRomID = entry.MameRomID
                };
                db.MameRomBios.InsertOnSubmit(mrb);
            }
            
            db.SubmitChanges();

            Dictionary<int, int> parentRomLookup = romBiosParse1.ToDictionary(a => a.MameRomID, b=> b.BiosMameRomID);

            // second parse - pick up clones, via their 'parent' roms which they are a clone of

            var romBiosParse2 = (from mr in db.MameRom
                         join mr2 in db.MameRom on mr.CloneOf equals mr2.RomName
                         where mr.CloneOf != null && mr.IsBios == 0 && parentRomLookup.Select(a=> a.Key).ToArray().Contains(mr2.MameRomID)
                         select new 
                         {
                               MameRomID = mr.MameRomID
                             , BiosMameRomID =  parentRomLookup[mr2.MameRomID]
                         }).ToList();

            foreach (var entry in romBiosParse2)
            {
                MameRomBios mrb = new MameRomBios()
                {
                    BiosMameRomID = entry.BiosMameRomID
                     ,
                    MameRomID = entry.MameRomID
                };
                db.MameRomBios.InsertOnSubmit(mrb);
            }

            db.SubmitChanges();


            return true;
        }


        private static bool populateMameControlsInfo(string controlFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {
            var controlDoc = XElement.Load(controlFile);

            string controlsVersion = (string)controlDoc.Element("meta").Element("version").Attribute("name"); 


            // load in from xml files
            List<ControlsInfo> controls = (from control in controlDoc.Descendants("game")
                                           select new ControlsInfo
                                           {
                                               romName = (string)control.Attribute("romname")
                                               ,
                                               miscdetails = (string)control.Element("miscDetails")
                                               ,
                                               gameName = (string)control.Attribute("gamename")
                                               ,
                                               numplayers = (int)control.Attribute("numPlayers")
                                               ,
                                               alternating = (bool)control.Attribute("alternating")
                                               ,
                                               mirrored = (bool)control.Attribute("mirrored")
                                               ,
                                               usesservice = (bool)control.Attribute("usesService")
                                               ,
                                               tilt = (bool)control.Attribute("tilt")
                                               ,
                                               cocktail = (bool)control.Attribute("cocktail")
                                           }
                             ).ToList();


            //_versions.Add("Controls: " + controlsVersion);


            if (!truncateTable(ceConn, "MameControlsInfo", "", ref error))
                return false;

            foreach (ControlsInfo control in controls)
            {
                if (romFileLookup.ContainsKey(control.romName))
                {
                    int mameRomID = romFileLookup[control.romName];

                    MameControlsInfo mci = new MameControlsInfo
                    {
                        MameRomID = mameRomID
                         ,
                        Alternating = (byte)(control.alternating ? 1 : 0)
                         ,
                        Cocktail = (byte)(control.cocktail ? 1 : 0)
                         ,
                        Mirrored = (byte)(control.mirrored ? 1 : 0)
                         ,
                        MiscDetails = control.miscdetails
                         ,
                        NumPlayers = (byte)control.numplayers
                         ,
                        Tilt = (byte)(control.tilt ? 1 : 0)
                         ,
                        UsesService = (byte)(control.usesservice ? 1 : 0)
                    };
                    db.MameControlsInfo.InsertOnSubmit(mci);

                }
                db.SubmitChanges();
            }

            populateMameFileInfo(db, "Controls", controlsVersion);

            return true;

        }

        private static bool populateMameCommand(string commandFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {


            // COMMAND
            List<Command> commands = new List<Command>();
            string[] buffer = null;

            try
            {
                buffer = File.ReadAllLines(commandFile);
            }
            catch (Exception e)
            {
                error = "Load Command file [" + commandFile + "] : " + e.Message;
                return false;
            }

            Command command = null;
            StringBuilder sb = null;
            bool read = false;
            List<string> romNames = null;
            bool skipNext = false;
            int order = 0;
            int commandSetId = 0;

            string commandVersion = "";

            // parse the file
            foreach (string line in buffer)
            {
                if (line.StartsWith("$info="))
                {
                    string temp = line.Substring(6);

                    string[] aTemp = temp.Split(',');
                    romNames = new List<string>();
                    foreach (string item in aTemp)
                    {
                        if (item != "")
                        {
                            romNames.Add(item.Trim());
                        }
                    }
                    order = 0;
                    commandSetId++;
                }

                if (line.StartsWith("$end"))
                {
                    read = false;
                    string temp = sb.ToString();
                    command.content = temp; //sb.ToString();
                    commands.Add(command);
                }

                if (read)
                {
                    sb.AppendLine(line);
                }

                if (skipNext)
                {
                    skipNext = false;
                    read = true;
                }

                if (line.StartsWith("["))
                {
                    command.heading = line.Substring(1, line.Length - 2).Replace("-", "");
                    skipNext = true;
                }

                if (line.StartsWith("$cmd"))
                {
                    sb = new StringBuilder();
                    //read = true;
                    command = new Command();
                    command.romNames = romNames;
                    command.order = ++order;
                    command.MameCommandSetID = commandSetId;
                }

                if (line.StartsWith("#") && line.Contains("Last Update:"))
                {
                    // extract version date...
                    string[] aVersTemp = line.Split(':');
                    commandVersion = aVersTemp[1].Trim('#').Trim();
                }
            }

           // _versions.Add("Command: " + commandVersion);

            if(!truncateTable(ceConn, "MameRomCommand", "", ref error))
                return false;
            if(!truncateTable(ceConn, "MameCommand", "", ref error))
                return false;


            int prevCommandSetID = -1;
            foreach (Command cmd in commands)
            {
                MameCommand mc = new MameCommand
                {
                    MameCommandSetID = cmd.MameCommandSetID
                     ,
                    Content = cmd.content
                     ,
                    Heading = (cmd.heading == null ? "" : cmd.heading)
                     ,
                    SortOrder = cmd.order
                };
                db.MameCommand.InsertOnSubmit(mc);

                if (prevCommandSetID != cmd.MameCommandSetID)
                {
                    prevCommandSetID = cmd.MameCommandSetID;

                    // insert a record for relationship
                    foreach (string romName in cmd.romNames)
                    {

                        if (romFileLookup.ContainsKey(romName))
                        {
                            MameRomCommand mrc = new MameRomCommand
                            {
                                MameCommandSetID = cmd.MameCommandSetID
                                 ,
                                MameRomID = romFileLookup[romName]
                            };
                            db.MameRomCommand.InsertOnSubmit(mrc);
                        }
                    }
                }
            }
            db.SubmitChanges();

            populateMameFileInfo(db, "Command", commandVersion);

            return true;

        }

        private static bool populateMamePlayers(string controlFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {
            var controlDoc = XElement.Load(controlFile);

            List<GamePlayer> gamePlayers = (
                from results in controlDoc.Descendants("game")
                select new GamePlayer
                {
                    romName = (string)results.Attribute("romname")
                    ,
                    players = (
                                from player in results.Descendants("player")
                                select new Player
                                {
                                    number = (string)player.Attribute("number")
                                    ,
                                    numButtons = (results == null ? -1 : (int)player.Attribute("numButtons"))
                                        // new labels list
                                    ,
                                    labels = (
                                         from i in player.Descendants("label")
                                         select new Label
                                         {
                                             name = (string)i.Attribute("name")
                                             ,
                                             value = (string)i.Attribute("value")
                                             ,
                                             color = null //  (string)i.Attribute("Color")
                                         }
                                     ).ToList()
                                        // new controls list
                                    ,
                                    controls = (
                                          from controlRow in player.Descendants("control")
                                          select new Control
                                          {
                                              name = (string)controlRow.Attribute("name")  // control name
                                              ,
                                              constants = (from k in controlRow.Descendants("constant") // control constants
                                                           select (string)k.Attribute("name")).ToList()
                                              ,
                                              buttons = (from l in controlRow.Descendants("button")
                                                         select (string)l.Attribute("name")).ToList()
                                          }
                                      ).ToList()
                                }).ToList()
                }
                ).ToList();


            if(!truncateTable(ceConn, "MameControlConstant", "", ref error))
                return false;
            if(!truncateTable(ceConn, "MameControlButton", "", ref error))
                return false;
            if(!truncateTable(ceConn, "MamePlayerControl", "MamePlayerControlID", ref error))
                return false;
            if(!truncateTable(ceConn, "MamePlayerLabel", "", ref error))
                return false;
            if(!truncateTable(ceConn, "MameConstant", "MameConstantID", ref error))
                return false;
            if(!truncateTable(ceConn, "MameButton", "MameButtonID", ref error))
                return false;
            if(!truncateTable(ceConn, "MameControl", "MameControlID", ref error))
                return false;
            if(!truncateTable(ceConn, "MameLabel", "MameLabelID", ref error))
                return false;
            if(!truncateTable(ceConn, "MamePlayer", "MamePlayerID", ref error))
                return false;




            // ****** MAMECONSTANT
            List<string> constants = (
                    from w in gamePlayers
                    where w.players != null
                    from x in w.players
                    from y in x.controls
                    from z in y.constants
                    select z
                    ).Distinct().ToList();

            constants.Sort();
            foreach (string constant in constants)
            {
                MameConstant mc = new MameConstant
                {
                    Constant = constant
                };
                db.MameConstant.InsertOnSubmit(mc);
            }
            db.SubmitChanges();
            constants.Clear();

            // ******MAMEBUTTON
            List<String> buttons = (
                from w in gamePlayers
                where w.players != null
                from x in w.players
                from y in x.controls
                from z in y.buttons

                select z

            ).Distinct().ToList();

            buttons.Sort();
            foreach (string button in buttons)
            {
                MameButton mb = new MameButton
                {
                    Button = button
                };
                db.MameButton.InsertOnSubmit(mb);
            }
            db.SubmitChanges();
            buttons.Clear();


            // ********MAMECONTROL
            List<String> controls = (
                from x in gamePlayers
                where x.players != null
                from y in x.players
                from z in y.controls

                select z.name

             ).Distinct().ToList();

            controls.Sort();
            foreach (string control in controls)
            {
                MameControl mc = new MameControl
                {
                    Control = control
                };
                db.MameControl.InsertOnSubmit(mc);
            }
            db.SubmitChanges();
            controls.Clear();

            // **********MAMELABEL


            List<Label> labels = (
                from x in gamePlayers
                where x.players != null
                from y in x.players
                from z in y.labels

                select new Label
                {
                    name = (z.name != null ? z.name.Trim() : null)
                    ,
                    color = (z.color != null ? z.color.Trim() : null)
                    ,
                    value = (z.value != null ? z.value.Trim() : null)
                }).Distinct(new LabelComparer()).ToList();


            foreach (Label label in labels)
            {
                MameLabel ml = new MameLabel
                {
                    Name = label.name
                     ,
                    Color = label.color
                     ,
                    Value = label.value
                };
                db.MameLabel.InsertOnSubmit(ml);
            }
            db.SubmitChanges();


            Dictionary<string, int> constantLookup = (from x in db.MameConstant
                                                      select x).ToDictionary(a => a.Constant, b => b.MameConstantID);

            Dictionary<string, int> buttonLookup = (from x in db.MameButton
                                                    select x).ToDictionary(a => a.Button, b => b.MameButtonID);

            Dictionary<string, int> controlLookup = (from x in db.MameControl
                                                     select x).ToDictionary(a => a.Control, b => b.MameControlID);

            Dictionary<string[], int> labelLookup = new Dictionary<string[], int>(new StringArrayComparer());

            {
                var labelTemp = from x in db.MameLabel
                                select new
                                {
                                    x.MameLabelID
                                    ,
                                    x.Color
                                    ,
                                    x.Name
                                    ,
                                    x.Value
                                };

                foreach (var labelItem in labelTemp)
                {
                    string[] lookup = new string[3];
                    lookup[0] = labelItem.Name;
                    lookup[1] = labelItem.Value;
                    lookup[2] = labelItem.Color;

                    labelLookup.Add(lookup, labelItem.MameLabelID);
                }
            }


            // now go through and populate remaining tables
            //int recordNum = 0;

            // *** MAMEPLAYER
            foreach (GamePlayer gamePlayer in gamePlayers)
            {
                string romName = gamePlayer.romName;
                // if romname valid, continue
                if (romFileLookup.ContainsKey(romName))
                {
                    int mameRomID = romFileLookup[romName];
                    foreach (Player player in gamePlayer.players)
                    {
                        MamePlayer mp = new MamePlayer
                        {
                            MameRomID = mameRomID
                             ,
                            Number = byte.Parse(player.number)
                             ,
                            NumberOfButtons = (byte)player.numButtons
                        };

                        db.MamePlayer.InsertOnSubmit(mp);

                    }


                }
            }
            db.SubmitChanges();

            Dictionary<int[], int> mamePlayerLookup = new Dictionary<int[], int>(new IntArrayComparer());
            {
                var test = (from x in db.MamePlayer
                            select new
                            {
                                x.MameRomID
                                ,
                                x.Number
                                ,
                                x.MamePlayerID
                            }).ToList();

                foreach (var entry in test)
                {
                    int[] lookup = new int[2];
                    lookup[0] = entry.MameRomID;
                    lookup[1] = entry.Number;

                    mamePlayerLookup.Add(lookup, entry.MamePlayerID);
                }
            }

            // MAME PLAYER CONTROL
            //recordNum = 0;
            foreach (GamePlayer gamePlayer in gamePlayers)
            {
                string romName = gamePlayer.romName;
                // if romname valid, continue
                if (romFileLookup.ContainsKey(romName))
                {
                    int mameRomID = romFileLookup[romName];
                    foreach (Player player in gamePlayer.players)
                    {
                        int number = int.Parse(player.number);
                        int[] lookup = new int[2];
                        lookup[0] = mameRomID;
                        lookup[1] = number;

                        if (mamePlayerLookup.ContainsKey(lookup))
                        {
                            int mamePlayerId = mamePlayerLookup[lookup];

                            // keep track of duplicate controls for same player
                            List<string> currentControls = new List<string>();

                            // control related
                            foreach (Control ctrl in player.controls)
                            {


                                if (controlLookup.ContainsKey(ctrl.name) && !currentControls.Contains(ctrl.name))
                                {

                                    int controlId = controlLookup[ctrl.name];
                                    MamePlayerControl mpc = new MamePlayerControl
                                    {
                                        MamePlayerID = mamePlayerId
                                         ,
                                        MameControlID = controlId
                                    };
                                    db.MamePlayerControl.InsertOnSubmit(mpc);
                                    currentControls.Add(ctrl.name);

                                }
                            }
                        }
                    }
                }
            }
            db.SubmitChanges();


            Dictionary<int[], int> mamePlayerControlLookup = new Dictionary<int[], int>(new IntArrayComparer());
            {

                var mpcQuery = from x in db.MamePlayerControl
                               select new
                               {
                                   x.MamePlayerID
                                   ,
                                   x.MameControlID
                                   ,
                                   x.MamePlayerControlID
                               };


                foreach (var mpcItem in mpcQuery)
                {

                    int[] mpcLookup = new int[2];
                    mpcLookup[0] = mpcItem.MamePlayerID;
                    mpcLookup[1] = mpcItem.MameControlID;
                    mamePlayerControlLookup.Add(mpcLookup, mpcItem.MamePlayerControlID);
                }

            }







            // *** THE REST
            // List<string> errors = new List<string>();
            //recordNum = 0;
            foreach (GamePlayer gamePlayer in gamePlayers)
            {
                string romName = gamePlayer.romName;
                // if romname valid, continue
                if (romFileLookup.ContainsKey(romName))
                {
                    int mameRomID = romFileLookup[romName];
                    foreach (Player player in gamePlayer.players)
                    {
                        int number = int.Parse(player.number);
                        int[] lookup = new int[2];
                        lookup[0] = mameRomID;
                        lookup[1] = number;

                        if (mamePlayerLookup.ContainsKey(lookup))
                        {
                            int mamePlayerId = mamePlayerLookup[lookup];

                            List<string> currentControls = new List<string>();

                            // control related
                            foreach (Control ctrl in player.controls)
                            {
                                if (controlLookup.ContainsKey(ctrl.name) && !currentControls.Contains(ctrl.name))
                                {
                                    // keep track of non duplicate controls
                                    currentControls.Add(ctrl.name);

                                    int controlId = controlLookup[ctrl.name];
                                    int[] mpcLookup = new int[2];
                                    mpcLookup[0] = mamePlayerId;
                                    mpcLookup[1] = controlId;
                                    if (mamePlayerControlLookup.ContainsKey(mpcLookup))
                                    {
                                        int mamePlayerControlId = mamePlayerControlLookup[mpcLookup];


                                        List<string> currentConstants = new List<string>();
                                        foreach (string constant in ctrl.constants)
                                        {
                                            if (constantLookup.ContainsKey(constant) && !currentConstants.Contains(constant))
                                            {
                                                currentConstants.Add(constant);
                                                int constantId = constantLookup[constant];
                                                MameControlConstant mcc = new MameControlConstant
                                                {
                                                    MamePlayerControlID = mamePlayerControlId
                                                     ,
                                                    MameConstantID = constantId
                                                };
                                                db.MameControlConstant.InsertOnSubmit(mcc);
                                            }
                                        }

                                        List<string> currentButtons = new List<string>();
                                        foreach (string button in ctrl.buttons)
                                        {
                                            if (buttonLookup.ContainsKey(button) && !currentButtons.Contains(button))
                                            {
                                                currentButtons.Add(button);
                                                int buttonId = buttonLookup[button];
                                                MameControlButton mcb = new MameControlButton
                                                {
                                                    MamePlayerControlID = mamePlayerControlId
                                                     ,
                                                    MameButtonID = buttonId
                                                };
                                                db.MameControlButton.InsertOnSubmit(mcb);
                                            }
                                        }
                                    }
                                }




                            }

                            // label related
                            Dictionary<string[], string> currentLabels = new Dictionary<string[], string>(new StringArrayComparer());
                            foreach (Label label in player.labels)
                            {
                                string[] lookupLabel = new string[3];
                                lookupLabel[0] = label.name;
                                lookupLabel[1] = label.value;
                                lookupLabel[2] = label.color;

                                if (labelLookup.ContainsKey(lookupLabel) && !currentLabels.ContainsKey(lookupLabel))
                                {
                                    currentLabels.Add(lookupLabel, "");

                                    int labelId = labelLookup[lookupLabel];
                                    MamePlayerLabel mpl = new MamePlayerLabel
                                    {
                                        MamePlayerID = mamePlayerId
                                        ,
                                        MameLabelID = labelId
                                    };
                                    db.MamePlayerLabel.InsertOnSubmit(mpl);
                                }
                            }

                        }


                    }

 
                }
            }
 
            db.SubmitChanges();
            gamePlayers.Clear();

            return true;
        }

        private static bool populateMameCategories(string categoryFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {

            // parse file
            List<Category> categories = new List<Category>();
            string[] buffer = File.ReadAllLines(categoryFile);

            bool write = false;
            foreach (string line in buffer)
            {
                if (line.StartsWith("["))
                {
                    string temp = line.Substring(1, line.Length - 2);
                    if (temp.ToUpper() == "CATEGORY")
                    {
                        write = true;
                    }
                    else
                    {
                        write = false;
                    }
                }
                else
                {
                    if (line != "" && !line.StartsWith(";") && line.Contains("=") && write)
                    {
                        string[] arrTemp = line.Split('=');

                        Category cat = new Category();
                        cat.categoryName = arrTemp[1];
                        cat.romName = arrTemp[0];
                        categories.Add(cat);
                    }
                }
            }



            // write distinct categories first
            List<String> mameCategories = (
                from x in categories
                select x.categoryName
            ).Distinct().ToList();

            mameCategories.Sort();



            if(!truncateTable(ceConn, "MameRomCategory", "", ref error))
                return false;
            if(!truncateTable(ceConn, "MameCategory", "MameCategoryID", ref error))
                return false;

            foreach (string category in mameCategories)
            {
                MameCategory mc = new MameCategory
                {
                    CategoryName = category
                };
                db.MameCategory.InsertOnSubmit(mc);
            }
            db.SubmitChanges();

            // now write rom-> categories
            Dictionary<string, int> categoryLookup = (from m in db.MameCategory
                                                      select m).ToDictionary(m => m.CategoryName, m => m.MameCategoryID);


            foreach (Category cat in categories)
            {

                if (categoryLookup.ContainsKey(cat.categoryName) && romFileLookup.ContainsKey(cat.romName))
                {
                    MameRomCategory mrc = new MameRomCategory
                    {
                        MameCategoryID = categoryLookup[cat.categoryName]
                        ,
                        MameRomID = romFileLookup[cat.romName]
                    };
                    db.MameRomCategory.InsertOnSubmit(mrc);
                }
            }
            db.SubmitChanges();
            categoryLookup.Clear();
            mameCategories.Clear();

            populateMameFileInfo(db, "CatVer", "Loaded:" + DateTime.Now.ToString("yyyyMMdd"));

            return true;
        }

        private static bool populateMameHistory(string historyFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {

            // parse file
            List<History> histories = new List<History>();
            string[] buffer = File.ReadAllLines(historyFile);
            History history = null;
            StringBuilder sb = null;
            bool read = false;

            string historyVersion = "";

            foreach (string line in buffer)
            {
                if (line.StartsWith("$info="))
                {
                    history = new History();
                    string temp = line.Substring(6);

                    string[] aTemp = temp.Split(',');
                    List<string> romnames = new List<string>();
                    foreach (string item in aTemp)
                    {
                        if (item != "")
                        {
                            romnames.Add(item);
                        }
                    }

                    history.romName = romnames;
                }

                if (line.StartsWith("$<a"))
                {
                    history.url = line.Substring(1);
                }

                if (line.StartsWith("$end"))
                {
                    read = false;
                    string temp = sb.ToString();
                    temp.Replace(Environment.NewLine + Environment.NewLine, Environment.NewLine);
                    history.content = temp; //sb.ToString();
                    histories.Add(history);
                }

                if (read)
                {
                    sb.AppendLine(line);
                }

                if (line.StartsWith("$bio"))
                {
                    sb = new StringBuilder();
                    read = true;
                }

                //## Revision: 1.36 - 2010-01-03
                if (line.StartsWith("## Revision"))
                {
                    historyVersion = line.Split(':')[1].Trim();
                }
            }

            // keep track of roms already processed, in case we have duplicates
            List<int> processedRoms = new List<int>();

            //_versions.Add("History: " + historyVersion);

            if(!truncateTable(ceConn, "MameHistory", "", ref error))
                return false;

            foreach (History hist in histories)
            {
                foreach (string romName in hist.romName)
                {
                    if (romFileLookup.ContainsKey(romName))
                    {
                        int mameRomID = romFileLookup[romName];

                        if (!processedRoms.Contains(mameRomID))
                        {
                            MameHistory mh = new MameHistory
                            {
                                MameRomID = romFileLookup[romName]
                                 ,
                                History = hist.content
                            };

                            db.MameHistory.InsertOnSubmit(mh);
                            processedRoms.Add(mameRomID);
                        }
                    }
                }
            }
            db.SubmitChanges();

            populateMameFileInfo(db, "History", historyVersion);

            return true;
        }

        private static bool populateMameRomDependencies(LauncherDB db, SqlCeConnection ceConn, ref string error)
        {


            if(!truncateTable(ceConn, "MameRomDependencies", "", ref error))
                return false;



            var romsWithBiosOnly = (from mr in db.MameRom
                                        join b in db.MameRom on mr.RomOf equals b.RomName
                                        where mr.CloneOf == null && mr.RomOf != null && mr.IsBios == 0
                                        && b.IsBios == 1
                                        select new { mr.MameRomID, mr.RomName, BiosMameRomID = b.MameRomID, BiosRomName = b.RomName }
                            ).ToList();

            foreach (var entry in romsWithBiosOnly)
            {
                MameRomDependencies mrd = new MameRomDependencies()
                {
                     MameRomID = entry.MameRomID
                     , RomName = entry.RomName
                     , BiosMameRomID = entry.MameRomID
                     , BiosRomName = entry.BiosRomName
                };
                db.MameRomDependencies.InsertOnSubmit(mrd);
            }
            db.SubmitChanges();
            romsWithBiosOnly = null;


            // clones (need to copy these PLUS file contained in Romof -> and then check game in romof for bios)
            //select *
            //from MameRom
            //where cloneof is not null and romof is not null and isBios = 0

            var cloneRoms =
                (from mr in db.MameRom
                 join pr in db.MameRom on mr.RomOf equals pr.RomName
                 join b in db.MameRom on pr.RomOf equals b.RomName into BiosRes
                 from br in BiosRes.DefaultIfEmpty()
                 where mr.CloneOf != null && mr.RomOf != null && mr.IsBios == 0
                 select new { mr.MameRomID, mr.RomName, ParentMameRomID = pr.MameRomID, ParentRomName = pr.RomName, BiosMameRomID = (int?)br.MameRomID, BiosRomName = br.RomName }
                ).ToList();

            foreach (var entry in cloneRoms)
            {
                MameRomDependencies mrd = new MameRomDependencies()
                {
                    MameRomID = entry.MameRomID
                     ,
                    RomName = entry.RomName
                     ,
                    ParentMameRomID = entry.ParentMameRomID
                     ,
                    ParentRomName = entry.ParentRomName
                     ,
                    BiosMameRomID = entry.BiosMameRomID
                     ,
                    BiosRomName = entry.BiosRomName
                };
                db.MameRomDependencies.InsertOnSubmit(mrd);

            }
            db.SubmitChanges();
            cloneRoms = null;

            return true;
        }

        private static bool populateMameNPlayers(string nPlayersFile, Dictionary<string, int> romFileLookup, LauncherDB db, SqlCeConnection ceConn, ref string error)
        {
            // parse file

            Dictionary<string, string> entries = new Dictionary<string, string>();
            List<string> processedRoms = new List<string>();

            string nPlayersVersion = "";
            bool noHeaderFound = true;

            string[] buffer = File.ReadAllLines(nPlayersFile);
            foreach (string line in buffer)
            {
                if (!line.StartsWith(";;") && !line.StartsWith("[") && line != "")
                {
                    string[] temp = line.Split('=');

                    string romName = temp[0];

                    if (!processedRoms.Contains(romName))
                    {

                        string nPlayers = temp[1];

                        entries.Add(romName, nPlayers);

                        processedRoms.Add(romName);
                    }

                }

                if (line.StartsWith(";;") && noHeaderFound)
                {
                    // only read in the first commented line - this should have the version details

                    //;; NPlayers 0.136 / 02-jan-10 / MAME .136 / http://nplayers.arcadebelgium.be ;;
                    nPlayersVersion = line.Trim(';').Split('/')[0].Trim();
                    noHeaderFound = false;
                }
            }
            processedRoms.Clear();

            //_versions.Add("nPlayers: " + nPlayersVersion);

            if(!truncateTable(ceConn, "MameNPlayers", "", ref error))
                return false;

            foreach (KeyValuePair<string, string> item in entries)
            {
                if (romFileLookup.ContainsKey(item.Key))
                {
                    MameNPlayers mnp = new MameNPlayers
                    {
                        MameRomID = romFileLookup[item.Key]
                        ,
                        NPlayers = item.Value
                    };

                    db.MameNPlayers.InsertOnSubmit(mnp);
                }
            }
            db.SubmitChanges();

            populateMameFileInfo(db, "NPlayers", nPlayersVersion);

            return true;
        }

        /// <summary>
        /// Populates any possible file meta data into a table for future reference..
        /// </summary>
        /// <param name="db">LauncherDB instance</param>
        /// <param name="fileName">Filename being loaded</param>
        /// <param name="details">Any relevant details/ metadata.</param>
        private static void populateMameFileInfo(LauncherDB db, string fileName, string details)
        {
            // check if entry already there

            MameFileInfo mfi = db.MameFileInfo.SingleOrDefault(x => x.FileName == fileName);

            if (mfi != null)
            {
                // update
                if (details.Length > 128)
                    mfi.Details = details.Substring(0, 128); // trim to 128 char field
                else
                    mfi.Details = details;
                db.SubmitChanges();
            }
            else
            {
                mfi = new MameFileInfo
                {
                     FileName = fileName
                     , Details = details
                };
                db.MameFileInfo.InsertOnSubmit(mfi);
                db.SubmitChanges();
            }

        }
    }
}
